package com.edu.dao;

import java.sql.*;

/**
 * @Author:czc
 * @Date: 2024/9/13 19:10
 * @Description:
 */
public class BaseDao {
    public static final String URL="jdbc:mysql://127.0.0.1:3306/library?";
    public static final String USER="root";
    public static final String PASS="123456";
    public static final String DRIVER="com.mysql.cj.jdbc.Driver";

    protected Connection conn=null;
    protected PreparedStatement ps = null;

    /**
     * 执行 添加 删除 修改的方法
     * @param: string SQL，Object占位符对应的值得数组 没有占位符传入null
     * @RETURN int 影响行数 -1没有执行
     * */
    public static int executeUpdate(String sql,Object[] param) {
        Connection conn=null;
        PreparedStatement ps = null;
        try {
            conn = getConn();
            ps = conn.prepareStatement(sql);//预编译sql
            if(param != null) {
                for (int i = 0; i < param.length; i++) {
                    ps.setObject(i+1, param[i]);
                }
            }
            int num = ps.executeUpdate();//执行sql
            return num;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            closeAll(conn, ps);
        }
        return -1;
    }

    /**
     * 执行 添加 删除 修改的方法
     * @param: string SQL
     * @RETURN int 影响行数
     * */
    public static int executeUpdate(String sql) {
        Connection conn=null;
        PreparedStatement ps = null;
        try {
            conn = getConn();
            ps = conn.prepareStatement(sql);//预编译sql
            int num = ps.executeUpdate();
            return num;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            closeAll(conn, ps);
        }
        return -1;
    }

    /**
     * 获得数据库连接
     * */
    public static Connection getConn() {
        try {
            Class.forName(DRIVER);
            Connection conn= DriverManager.getConnection(URL, USER, PASS);
            if(conn != null) {
                System.out.println("连接成功");
            }else {
                System.out.println("数据库连接失败");
            }
            return conn;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return null;
    }
    /**
     * 释放资源Connection，PreparedStatement，ResultSet
     * */
    public static void closeAll(Connection conn, Statement ps, ResultSet rs) {
        try {
            if(rs !=null) {rs.close();}
            if(ps !=null) {ps.close();}
            if(conn !=null) {conn.close();}
        } catch (SQLException e) {e.printStackTrace();}
    }

    /**
     * 释放资源Connection，PreparedStatement
     * */
    public static void closeAll(Connection conn,PreparedStatement ps) {
        try {
            if(ps !=null) {ps.close();}
            if(conn !=null) {conn.close();}
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


    /**
     * 查询
     * @param sql
     * @return ResultSet
     * */
    public ResultSet queryResultSet(String sql) {
        Connection conn=BaseDao.getConn();//打开连接
        PreparedStatement ps= null;
        ResultSet rs=null;
        try {
            ps= conn.prepareStatement(sql);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }


    /**
     * 查询案例
     * 可借鉴
     * */

//	public User query(String username, String userpass) {
//		Connection conn=BaseDao.getConn();
//		PreparedStatement s = null;
//		ResultSet rs = null;
//		try {
//			if(conn!=null) {
//				System.out.println("连接成功");
//			}
//			String sql = "select * from user where username='"+username+"' and userpass='"+userpass+"'";
//			System.out.println(sql);
//			s = conn.prepareStatement(sql);
//			rs =  s.executeQuery();
//			while(rs.next()) {
//				User user = new User();
//				user.setId(rs.getInt("id"));
//				user.setUsername(rs.getString("username"));
//				user.setUserpass(rs.getString("userpass"));
//				user.setSex(rs.getString("sex"));
//				user.setAge(rs.getInt("age"));
//				return user;
//			}
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			BaseDao.closeAll(conn, s,rs);
//		}
//		return null;
//	}

//	public List<User> queryAll() {
//		Connection conn=BaseDao.getConn();//打开连接
//		String sql = "SELECT * FROM user";
//		List<User> list = new ArrayList<User>();
//		PreparedStatement ps= null;
//		ResultSet rs=null;
//		try {
//			ps= conn.prepareStatement(sql);
//			rs = ps.executeQuery();
//			while(rs.next()) {
//				User user = new User();
//				user.setId(rs.getInt("id"));
//				user.setUsername(rs.getString("username"));
//				user.setUserpass(rs.getString("userpass"));
//				user.setSex(rs.getString("sex"));
//				user.setAge(rs.getInt("age"));
//				list.add(user);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			BaseDao.closeAll(conn, ps,rs);
//		}
//		return list;
//	}
}
